package com.ken.core;

import com.google.common.collect.Lists;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.SAXHelper;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.springframework.core.convert.ConversionService;
import org.springframework.core.convert.support.DefaultConversionService;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;

import javax.xml.parsers.ParserConfigurationException;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.LinkedList;


/**
 * Excle xxls 批量读取大文件操作类
 * 通过继承该抽象类后，实现自己的获取excel文件流方法,包括且不限于读取本地文件，网络文件，接收文件上传等等
 */
public abstract class XlsxProcessAbstract {

    Logger log = Logger.getLogger(XlsxProcessAbstract.class);

    private final int minColumns = 0;

    /**
     * Destination for data
     *
     * @param sheetNum 第几个sheet
     * @param clazz    pojo类型
     * @param <T>
     * @return
     * @throws Exception
     */
    public <T> LinkedList<T> processAllSheet(Integer sheetNum, Class<T> clazz) throws Exception {
        OPCPackage pkg = OPCPackage.open(this.getInputStream());
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
        XSSFReader xssfReader = new XSSFReader(pkg);
        StylesTable styles = xssfReader.getStylesTable();
        SheetToCSV<T> sheetToCSV = new SheetToCSV<T>(clazz);
        parserSheetXml(styles, strings, sheetToCSV, xssfReader.getSheet("rId" + sheetNum));
        return sheetToCSV.getPojoList();
    }

    /**
     * 解析excel 转换成xml
     *
     * @param styles
     * @param strings
     * @param sheetHandler
     * @param sheetInputStream
     * @throws IOException
     * @throws SAXException
     */
    public void parserSheetXml(StylesTable styles, ReadOnlySharedStringsTable strings, SheetContentsHandler sheetHandler, InputStream sheetInputStream) throws IOException, SAXException {
        DataFormatter formatter = new DataFormatter();
        InputSource sheetSource = new InputSource(sheetInputStream);
        try {
            XMLReader sheetParser = SAXHelper.newXMLReader();
            ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings, sheetHandler, formatter, false);
            sheetParser.setContentHandler(handler);
            sheetParser.parse(sheetSource);
        } catch (ParserConfigurationException e) {
            throw new RuntimeException("SAX parser appears to be broken - " + e);
        }
    }

    public abstract InputStream getInputStream() throws IOException;

    /**
     * 读取excel行、列值
     *
     * @author nevin.zhang
     */
    private class SheetToCSV<T> implements SheetContentsHandler {
        private T pojo;
        private Class<T> clazz;
        /**
         * 当前行
         */
        private int currentRowNumber = -1;
        /**
         * 保存Excel中第一行的所有值，需要与pojo值能够对应上
         */
        private ArrayList<PropertyDescriptor> propertyDescriptors = Lists.newArrayList();
        /**
         * 将每行数据反序列化成pojo后存入list，因为不确定行数所以用linkedList减少扩容时内存溢出风险，待优化
         */
        private LinkedList<T> pojoList = Lists.newLinkedList();
        /**
         * springMVC的类型转换器,将string类型值转换成pojo中属性的值
         */
        private ConversionService conversionService = new DefaultConversionService();

        public LinkedList<T> getPojoList() {
            return pojoList;
        }

        public SheetToCSV(Class<T> clazz) {
            this.clazz = clazz;
        }


        /**
         * 开始读一行
         *
         * @param rowNum
         */
        @Override
        public void startRow(int rowNum) {
            if (rowNum != 0) {
                try {
//                    反射生成pojo
                    pojo = clazz.newInstance();
                } catch (InstantiationException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
            currentRowNumber = rowNum;
        }

        /**
         * 读行结束，将pojo放入list中
         *
         * @param rowNum
         */
        @Override
        public void endRow(int rowNum) {
            if (pojo == null) {
                return;
            }
            if (currentRowNumber != 0) {
                pojoList.add(pojo);
            }

        }

        @Override
        public void cell(String cellReference, String cellValue, XSSFComment comment) {
            // 通过excel坐标获取当前行数
            int currentColNumber = (new CellReference(cellReference)).getCol();
            if (currentRowNumber == 0) {
                //如果是第0行，则将该行每个值都当作pojo中的属性名，通过反射获取属性描述器，放入list中
                PropertyDescriptor pd = null;
                if (StringUtils.isNotBlank(cellValue)) {
                    try {
                        pd = new PropertyDescriptor(cellValue, clazz);
                    } catch (IntrospectionException e) {
                        log.error("根据excel第一行第" + currentColNumber + "列值" + cellValue + "获取pojo属性描述器失败", e);
                    }
                }
                propertyDescriptors.add(pd);
                return;
            }
            if (pojo == null || StringUtils.isBlank(cellValue)) {
                //pojo或者cell值未空时不作处理
                return;
            }
            PropertyDescriptor pd = propertyDescriptors.get(currentColNumber);
            if (pd == null) {
                // 该列获取属性描述器为空,该列不做处理
                return;
            }
            try {
                // 获取pojo该属性的setter方法
                Method setMethod = pd.getWriteMethod();
                // 使用Spring类型转换器，将string类型的cell值转换成pojo该属性的类型，将调用set方法赋值
                setMethod.invoke(pojo, conversionService.convert(cellValue, pd.getPropertyType()));
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }

        }

        @Override
        public void headerFooter(String text, boolean isHeader, String tagName) {
        }

    }
}

